﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using IronOne.SalesOne.BusinessObjects;
using System.Linq.Dynamic;
using System.Configuration;

namespace IronOne.SalesOne.DataObjects
{
    public class LinqCustomer
    {
        public int pageSize = Convert.ToInt32(ConfigurationManager.AppSettings["GridCount"]);

        public List<Customer> GetCustomers(string sortExpression)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                IQueryable<CustomerEntity> query = db.CustomerEntities;

                if (sortExpression.Length > 0)
                {
                    string[] sort = sortExpression.Split(' ');
                    string sortColumn = sort[0];
                    string sortOrder = sort[1];

                    switch (sortColumn)
                    {
                        case "CustomerID":
                            if (sortOrder == "ASC")
                                query = query.OrderBy(c => c.CustomerID);
                            else
                                query = query.OrderByDescending(c => c.CustomerID);
                            break;
                       
                        case "FirstName":
                            if (sortOrder == "ASC")
                                query = query.OrderBy(c => c.FirstName);
                            else
                                query = query.OrderByDescending(c => c.FirstName);
                            break;
                        case "LastName":
                            if (sortOrder == "ASC")
                                query = query.OrderBy(c => c.LastName);
                            else
                                query = query.OrderByDescending(c => c.LastName);
                            break;
                        case "IDNumber":
                            if (sortOrder == "ASC")
                                query = query.OrderBy(c => c.IDNumber);
                            else
                                query = query.OrderByDescending(c => c.IDNumber);
                            break;

                    }
                }
                return query.Select(c => Mapper.ToCustomerObject(c)).ToList();
            }
        }


        //public List<NeedsAnalysis> GetNeedAnalysisCustomers(NeedsAnalysis needAnalysis)
        //{
        //   StringBuilder qry = new StringBuilder();


        //    using (SODataDataContext db = DataContextFactory.CreateContext())
        //    {
        //        //if (product.ProductCategoryCode != 0)
        //        //{
        //        //    qry.Append("ProductCategoryCode=" + product.ProductCategoryCode);
        //        // if (needAnalysis.IdNo != "" && needAnalysis.IdType != "")
        //        if (needAnalysis.IdNo != "")
        //        {
        //            qry = qry.Append(" CustomerID =\"" + needAnalysis.IdNo + "\"");
        //        }
        //        if (needAnalysis.IdNo != "" && needAnalysis.ProspectId != "" && needAnalysis.NeedAnalysisNo != 0)
        //        {
        //            qry = qry.Append(" AND " + " ProspectID =\"" + needAnalysis.ProspectId + "\"");

        //        }
        //        //else if (needAnalysis.IdNo != "" && needAnalysis.NeedAnalysisNo != 0)
        //        //{
        //        //    qry = qry.Append(" AND " + (" NeedsAnalysisNo = " + needAnalysis.NeedAnalysisNo));

        //        //}
        //            //else if (product.PlanName != "" && product.PlanCode == "")
        //            //{
        //            //    qry = qry.Append(" AND " + " PlanName.Contains(\"" + product.PlanName + "\")");
                        
        //            //}

                

                
        //        IQueryable<NeedsAnalysisDetailEntity> query = db.NeedsAnalysisDetailEntities;

             
        //        query = db.NeedsAnalysisDetailEntities.Where(qry.ToString());
        //        //var pageData = query.Skip((page - 1) * pageSize).Take(pageSize);
        //        return query.Select(c => Mapper.ToNeedsAnalysisObject(c)).ToList();
               
        //    }
        //}

        




        public Customer GetCustomer(string customerId)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                return Mapper.ToCustomerObject(db.CustomerEntities
                            .SingleOrDefault(p => p.CustomerID == customerId));
            }
        }




        public void InsertCustomer(Customer customer)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                try
                {
                    CustomerEntity entity = Mapper.ToCustomerEntity(customer);
                    db.CustomerEntities.InsertOnSubmit(entity);
                    db.SubmitChanges();

                    customer.CustomerId = entity.CustomerID;
                }
                catch (ChangeConflictException)
                {
                    throw new Exception("A change to contact record was made before your changes.");
                }
                catch (Exception)
                {
                    throw new Exception("Unable to Insert contact " + customer.CustomerId);
                }
            }
        }


        public int UpdateCustomer(Customer customer)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                try
                {
                    CustomerEntity entity = Mapper.ToCustomerEntity(customer);
                    db.CustomerEntities.Attach(entity, true);
                    db.SubmitChanges();
                    return 1;
                }
                catch (ChangeConflictException)
                {
                    throw new Exception("A change to contact record was made before your changes.");
                }
                catch (Exception)
                {
                    return 0;
                }
            }
        }


        public int DeleteCustomer(Customer customer)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                try
                {
                    CustomerEntity entity = Mapper.ToCustomerEntity(customer);
                    db.CustomerEntities.Attach(entity, false);
                    db.CustomerEntities.DeleteOnSubmit(entity);
                    db.SubmitChanges();

                    return 1;
                }
                catch (ChangeConflictException)
                {
                    throw new Exception("A change to contact record was made before your changes.");
                }
                catch (Exception)
                {
                    return 0;
                }
            }
        }


        public Customer GetCustomerByContractId(string contractId)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                CustomerEntity customer = (from cus in db.CustomerEntities
                               join policy in db.PolicyEntities on cus.CustomerID equals policy.CustomerID
                               where policy.ContractId == contractId
                               select cus).First();

                return Mapper.ToCustomerObject(customer);
            }
        }





        //public string GetSOForClientPolicy(string contId)
        //{ 
        //    using (SODataDataContext db = DataContextFactory.CreateContext())
        //    {
        //        IQueryable<string> query; //
        //       // string qu
        //        query = from pol in db.PolicyEntities
        //                join cust in db.CustomerEntities on pol.CustomerID equals cust.CustomerID
        //                where cust.CustomerID == customerId
        //                select new { user.SOCode }.SOCode;

        //        //query = from user in db.UserEntities
        //        //        join cust in db.CustomerEntities on user.UserId equals cust.UserID
        //        //        where cust.CustomerID == customerId
        //        //        select new { user.SOCode }.SOCode;

        //        return query.SingleOrDefault();
                
        //    }
        //}







        public List<Customer> SearchCustomers(ContactSearch cust, string type, int pageNo)
        {
            try
            {
                StringBuilder str = new StringBuilder();

                //unit
                if (cust.Unt != null)
                {
                    int j = 0;
                    foreach (SalesOneUnit un in cust.Unt)
                    {

                        if (j == 0)
                        {
                            if (un.UnitId != 0)
                                str.Append(" AND (NodeId=" + un.UnitId.ToString() + " AND UserTypeId = 4)");

                        }
                        else
                        {
                            if (un.UnitId != 0)
                                str.Append(" OR (NodeId=" + un.UnitId.ToString() + " AND UserTypeId = 4)");

                        }

                        j++;
                    }

                }



                //branch
                if (cust.Brnch != null)
                {
                    int k = 0;
                    foreach (Branch br in cust.Brnch)
                    {

                        if (k == 0)
                        {
                            if (br.BranchId != 0 && cust.Unt == null)
                                str.Append(" AND (NodeId=" + br.BranchId.ToString() + " AND UserTypeId = 3)");
                        }
                        else
                        {
                            if (br.BranchId != 0 && cust.Unt == null)
                                str.Append(" OR (NodeId=" + br.BranchId.ToString() + " AND UserTypeId = 3)");
                        }

                        k++;
                    }
                }



                //so
                StringBuilder sb = new StringBuilder();
                if (cust.SOCode != null)
                {
                    int i = 0;
                    foreach (string so in cust.SOCode)
                    {
                        if (i == 0)
                        {
                            if (!String.IsNullOrEmpty(so) && so != "Select")
                                sb.Append(" AND SOCode = \"" + so + "\"");
                        }
                        else
                        {
                            if (!String.IsNullOrEmpty(so) && so != "Select")
                                sb.Append(" OR SOCode = \"" + so + "\"");
                        }

                        i++;
                    }
                }

                


                //StringBuilder cus = new StringBuilder();
                string status = type;

                var cusPred = PredicateBuilder.True<CustomerEntity>();
                cusPred = cusPred.And(s => s.Status == status);
                if (!String.IsNullOrEmpty(cust.CliemtId))
                    cusPred = cusPred.And(s => s.CustomerID == cust.CliemtId);
                if (!String.IsNullOrEmpty(cust.Name))
                    cusPred = cusPred.And(s => s.FirstName.Contains(cust.Name));
                if (!String.IsNullOrEmpty(cust.IdNo) && !String.IsNullOrEmpty(cust.IdType))
                {
                    cusPred = cusPred.And(s => s.IDNumber == cust.IdNo);
                    cusPred = cusPred.And(s => s.IDType == int.Parse(cust.IdType));
                }


                //cus.Append("Status = \"" + status + "\"");
                //if (!String.IsNullOrEmpty(cust.CliemtId))
                //    cus.Append(" AND CustomerID =  \"" + cust.CliemtId + "\"");
                //if (!String.IsNullOrEmpty(cust.Name))
                //    cus.Append(" AND FirstName LIKE \"%" + cust.Name + "%\"");
                //if (!String.IsNullOrEmpty(cust.IdNo) && !String.IsNullOrEmpty(cust.IdType))
                //    cus.Append(" AND IDNumber = \"" + cust.IdNo + "\"" + " AND IDType = " + cust.IdType);

                


                //if client
                StringBuilder pol = new StringBuilder();
                if (!String.IsNullOrEmpty(cust.Contact))
                    pol.Append(" AND ContractId = \"" + cust.Contact + "\"");



                //if (cus.ToString().StartsWith(" AND "))
                //    cus.Remove(0, 5);
                if (str.ToString().StartsWith(" AND "))
                    str.Remove(0, 5);
                if (sb.ToString().StartsWith(" AND "))
                    sb.Remove(0, 5);
                if (pol.ToString().StartsWith(" AND "))
                    pol.Remove(0, 5);


                using (SODataDataContext db = DataContextFactory.CreateContext())
                {
                    IQueryable<CustomerEntity> query = db.CustomerEntities;



                    if (type.Equals("Client"))
                    {
                        if (pol.Length > 1)
                        {
                            if (str.Length > 1 && sb.Length > 1)
                            {
                                IQueryable<Customer> queryo = (from c in db.CustomerEntities.Where(cusPred)
                                                               join po in db.PolicyEntities.Where(pol.ToString())
                                                               on c.CustomerID equals po.CustomerID
                                                               join u in db.UserEntities.Where(sb.ToString())
                                                               on po.SOCode equals u.SOCode
                                                               join us in db.UsersInStructureEntities.Where(str.ToString())
                                                               on u.UserId equals us.UserId
                                                               select new Customer
                                                               {
                                                                   CustomerId = c.CustomerID,
                                                                   FirstName = c.FirstName,
                                                                   LastName = c.LastName,
                                                                   Status = c.Status,
                                                                   IdNumber = c.IDNumber,
                                                                   IdType = c.IDType == null ? String.Empty : c.IDTypeEntity.Description,
                                                                   DOB = c.DOB == null ? string.Empty : c.DOB.Value.ToShortDateString(),
                                                                   ContactNo = c.ContractNo,
                                                                   MaritalStatus = c.MaritalStatus == null ? String.Empty : c.MaritalStatusEntity.Description,
                                                                   Profession = c.Profession,
                                                                   Country = c.Country == null ? String.Empty : c.CountryEntity.Description,
                                                                   State = c.State,
                                                                   OtherName = c.OtherName,
                                                                   HomeTelephone = c.HomeTelephone,
                                                                   OfficialTelephone = c.OfficeTelephone,
                                                                   EMail = c.Email,
                                                                   Mobile = c.Mobile,
                                                                   Address1 = c.Address1,
                                                                   Address2 = c.Address2,
                                                                   City = c.City,
                                                                   Gender = c.Gender,
                                                                   SoCode = po.SOCode,
                                                                   Remarks = c.Remarks,
                                                                   LastUpdated = c.LastUpdated == null ? string.Empty : c.LastUpdated.Value.ToShortDateString(),
                                                                   Title = c.Title == null ? String.Empty : c.SalutationEntity.Description.ToString()
                                                               }).Distinct();

                                return queryo.Skip((pageNo - 1) * pageSize).Take(pageSize).ToList();
                            }
                            else if (str.Length > 1 && sb.Length < 1)
                            {
                                IQueryable<Customer> queryo = (from c in db.CustomerEntities.Where(cusPred)
                                                               join po in db.PolicyEntities.Where(pol.ToString())
                                                               on c.CustomerID equals po.CustomerID
                                                               join u in db.UserEntities
                                                               on po.SOCode equals u.SOCode
                                                               join us in db.UsersInStructureEntities.Where(str.ToString())
                                                               on u.UserId equals us.UserId
                                                               select new Customer
                                                               {
                                                                   CustomerId = c.CustomerID,
                                                                   FirstName = c.FirstName,
                                                                   LastName = c.LastName,
                                                                   Status = c.Status,
                                                                   IdNumber = c.IDNumber,
                                                                   IdType = c.IDType == null ? String.Empty : c.IDTypeEntity.Description,
                                                                   DOB = c.DOB == null ? string.Empty : c.DOB.Value.ToShortDateString(),
                                                                   ContactNo = c.ContractNo,
                                                                   MaritalStatus = c.MaritalStatus == null ? String.Empty : c.MaritalStatusEntity.Description,
                                                                   Profession = c.Profession,
                                                                   Country = c.Country == null ? String.Empty : c.CountryEntity.Description,
                                                                   State = c.State,
                                                                   OtherName = c.OtherName,
                                                                   HomeTelephone = c.HomeTelephone,
                                                                   OfficialTelephone = c.OfficeTelephone,
                                                                   EMail = c.Email,
                                                                   Mobile = c.Mobile,
                                                                   Address1 = c.Address1,
                                                                   Address2 = c.Address2,
                                                                   City = c.City,
                                                                   Gender = c.Gender,
                                                                   SoCode = po.SOCode,
                                                                   Remarks = c.Remarks,
                                                                   LastUpdated = c.LastUpdated == null ? string.Empty : c.LastUpdated.Value.ToShortDateString(),
                                                                   Title = c.Title == null ? String.Empty : c.SalutationEntity.Description.ToString()
                                                               }).Distinct();

                                return queryo.Skip((pageNo - 1) * pageSize).Take(pageSize).ToList();
                            }
                            else if (str.Length < 1 && sb.Length > 1)
                            {
                                IQueryable<Customer> queryo = (from c in db.CustomerEntities.Where(cusPred)
                                                               join po in db.PolicyEntities.Where(pol.ToString())
                                                               on c.CustomerID equals po.CustomerID
                                                               join u in db.UserEntities.Where(sb.ToString())
                                                               on po.SOCode equals u.SOCode
                                                               select new Customer
                                                               {
                                                                   CustomerId = c.CustomerID,
                                                                   FirstName = c.FirstName,
                                                                   LastName = c.LastName,
                                                                   Status = c.Status,
                                                                   IdNumber = c.IDNumber,
                                                                   IdType = c.IDType == null ? String.Empty : c.IDTypeEntity.Description,
                                                                   DOB = c.DOB == null ? string.Empty : c.DOB.Value.ToShortDateString(),
                                                                   ContactNo = c.ContractNo,
                                                                   MaritalStatus = c.MaritalStatus == null ? String.Empty : c.MaritalStatusEntity.Description,
                                                                   Profession = c.Profession,
                                                                   Country = c.Country == null ? String.Empty : c.CountryEntity.Description,
                                                                   State = c.State,
                                                                   OtherName = c.OtherName,
                                                                   HomeTelephone = c.HomeTelephone,
                                                                   OfficialTelephone = c.OfficeTelephone,
                                                                   EMail = c.Email,
                                                                   Mobile = c.Mobile,
                                                                   Address1 = c.Address1,
                                                                   Address2 = c.Address2,
                                                                   City = c.City,
                                                                   Gender = c.Gender,
                                                                   SoCode = po.SOCode,
                                                                   Remarks = c.Remarks,
                                                                   LastUpdated = c.LastUpdated == null ? string.Empty : c.LastUpdated.Value.ToShortDateString(),
                                                                   Title = c.Title == null ? String.Empty : c.SalutationEntity.Description.ToString()
                                                               }).Distinct();

                                return queryo.Skip((pageNo - 1) * pageSize).Take(pageSize).ToList();
                            }
                            else if (str.Length < 1 && sb.Length < 1)
                            {
                                IQueryable<Customer> queryo = (from c in db.CustomerEntities.Where(cusPred)
                                                               join po in db.PolicyEntities.Where(pol.ToString())
                                                               on c.CustomerID equals po.CustomerID
                                                               select new Customer
                                                               {
                                                                   CustomerId = c.CustomerID,
                                                                   FirstName = c.FirstName,
                                                                   LastName = c.LastName,
                                                                   Status = c.Status,
                                                                   IdNumber = c.IDNumber,
                                                                   IdType = c.IDType == null ? String.Empty : c.IDTypeEntity.Description,
                                                                   DOB = c.DOB == null ? string.Empty : c.DOB.Value.ToShortDateString(),
                                                                   ContactNo = c.ContractNo,
                                                                   MaritalStatus = c.MaritalStatus == null ? String.Empty : c.MaritalStatusEntity.Description,
                                                                   Profession = c.Profession,
                                                                   Country = c.Country == null ? String.Empty : c.CountryEntity.Description,
                                                                   State = c.State,
                                                                   OtherName = c.OtherName,
                                                                   HomeTelephone = c.HomeTelephone,
                                                                   OfficialTelephone = c.OfficeTelephone,
                                                                   EMail = c.Email,
                                                                   Mobile = c.Mobile,
                                                                   Address1 = c.Address1,
                                                                   Address2 = c.Address2,
                                                                   City = c.City,
                                                                   Gender = c.Gender,
                                                                   SoCode = po.SOCode,
                                                                   Remarks = c.Remarks,
                                                                   LastUpdated = c.LastUpdated == null ? string.Empty : c.LastUpdated.Value.ToShortDateString(),
                                                                   Title = c.Title == null ? String.Empty : c.SalutationEntity.Description.ToString()
                                                               }).Distinct();

                                return queryo.Skip((pageNo - 1) * pageSize).Take(pageSize).ToList();
                            }
                        }
                        else
                        {
                            if (str.Length > 1 && sb.Length > 1)
                            {

                                IQueryable<Customer> queryo = (from c in db.CustomerEntities.Where(cusPred)
                                                               join p in db.PolicyEntities on c.CustomerID equals p.CustomerID
                                                               join u in db.UserEntities.Where(sb.ToString())
                                                               on p.SOCode equals u.SOCode
                                                               join us in db.UsersInStructureEntities.Where(str.ToString())
                                                               on u.UserId equals us.UserId
                                                               select new Customer
                                                               {
                                                                   CustomerId = c.CustomerID,
                                                                   FirstName = c.FirstName,
                                                                   LastName = c.LastName,
                                                                   Status = c.Status,
                                                                   IdNumber = c.IDNumber,
                                                                   IdType = c.IDType == null ? String.Empty : c.IDTypeEntity.Description,
                                                                   DOB = c.DOB == null ? string.Empty : c.DOB.Value.ToShortDateString(),
                                                                   ContactNo = c.ContractNo,
                                                                   MaritalStatus = c.MaritalStatus == null ? String.Empty : c.MaritalStatusEntity.Description,
                                                                   Profession = c.Profession,
                                                                   Country = c.Country == null ? String.Empty : c.CountryEntity.Description,
                                                                   State = c.State,
                                                                   OtherName = c.OtherName,
                                                                   HomeTelephone = c.HomeTelephone,
                                                                   OfficialTelephone = c.OfficeTelephone,
                                                                   EMail = c.Email,
                                                                   Mobile = c.Mobile,
                                                                   Address1 = c.Address1,
                                                                   Address2 = c.Address2,
                                                                   City = c.City,
                                                                   Gender = c.Gender,
                                                                   SoCode = p.SOCode,
                                                                   Remarks = c.Remarks,
                                                                   LastUpdated = c.LastUpdated == null ? string.Empty : c.LastUpdated.Value.ToShortDateString(),
                                                                   Title = c.Title == null ? String.Empty : c.SalutationEntity.Description.ToString()
                                                               }).Distinct();

                                return queryo.Skip((pageNo - 1) * pageSize).Take(pageSize).ToList();



                            }
                            else if (str.Length > 1 && sb.Length < 1)
                            {


                                IQueryable<Customer> queryo = (from c in db.CustomerEntities.Where(cusPred)
                                                               join p in db.PolicyEntities on c.CustomerID equals p.CustomerID
                                                               join u in db.UserEntities on p.SOCode equals u.SOCode
                                                               join us in db.UsersInStructureEntities.Where(str.ToString())
                                                               on u.UserId equals us.UserId
                                                               select new Customer
                                                               {
                                                                   CustomerId = c.CustomerID,
                                                                   FirstName = c.FirstName,
                                                                   LastName = c.LastName,
                                                                   Status = c.Status,
                                                                   IdNumber = c.IDNumber,
                                                                   IdType = c.IDType == null ? String.Empty : c.IDTypeEntity.Description,
                                                                   DOB = c.DOB == null ? string.Empty : c.DOB.Value.ToShortDateString(),
                                                                   ContactNo = c.ContractNo,
                                                                   MaritalStatus = c.MaritalStatus == null ? String.Empty : c.MaritalStatusEntity.Description,
                                                                   Profession = c.Profession,
                                                                   Country = c.Country == null ? String.Empty : c.CountryEntity.Description,
                                                                   State = c.State,
                                                                   OtherName = c.OtherName,
                                                                   HomeTelephone = c.HomeTelephone,
                                                                   OfficialTelephone = c.OfficeTelephone,
                                                                   EMail = c.Email,
                                                                   Mobile = c.Mobile,
                                                                   Address1 = c.Address1,
                                                                   Address2 = c.Address2,
                                                                   City = c.City,
                                                                   Gender = c.Gender,
                                                                   SoCode = p.SOCode,
                                                                   Remarks = c.Remarks,
                                                                   LastUpdated = c.LastUpdated == null ? string.Empty : c.LastUpdated.Value.ToShortDateString(),
                                                                   Title = c.Title == null ? String.Empty : c.SalutationEntity.Description.ToString()
                                                               }).Distinct();

                                return queryo.Skip((pageNo - 1) * pageSize).Take(pageSize).ToList();



                                //Select(c => Mapper.ToCustomerObject(c)).ToList();

                            }
                            else if (str.Length < 1 && sb.Length > 1)
                            {
                                IQueryable<Customer> queryo = (from c in db.CustomerEntities.Where(cusPred)
                                                               join p in db.PolicyEntities on c.CustomerID equals p.CustomerID
                                                               join u in db.UserEntities.Where(sb.ToString())
                                                               on p.SOCode equals u.SOCode
                                                               select new Customer
                                                               {
                                                                   CustomerId = c.CustomerID,
                                                                   FirstName = c.FirstName,
                                                                   LastName = c.LastName,
                                                                   Status = c.Status,
                                                                   IdNumber = c.IDNumber,
                                                                   IdType = c.IDType == null ? String.Empty : c.IDTypeEntity.Description,
                                                                   DOB = c.DOB == null ? string.Empty : c.DOB.Value.ToShortDateString(),
                                                                   ContactNo = c.ContractNo,
                                                                   MaritalStatus = c.MaritalStatus == null ? String.Empty : c.MaritalStatusEntity.Description,
                                                                   Profession = c.Profession,
                                                                   Country = c.Country == null ? String.Empty : c.CountryEntity.Description,
                                                                   State = c.State,
                                                                   OtherName = c.OtherName,
                                                                   HomeTelephone = c.HomeTelephone,
                                                                   OfficialTelephone = c.OfficeTelephone,
                                                                   EMail = c.Email,
                                                                   Mobile = c.Mobile,
                                                                   Address1 = c.Address1,
                                                                   Address2 = c.Address2,
                                                                   City = c.City,
                                                                   Gender = c.Gender,
                                                                   SoCode = p.SOCode,
                                                                   Remarks = c.Remarks,
                                                                   LastUpdated = c.LastUpdated == null ? string.Empty : c.LastUpdated.Value.ToShortDateString(),
                                                                   Title = c.Title == null ? String.Empty : c.SalutationEntity.Description.ToString()
                                                               }).Distinct();

                                return queryo.Skip((pageNo - 1) * pageSize).Take(pageSize).ToList();

                            }
                            else if (str.Length < 1 && sb.Length < 1)
                            {
                                IQueryable<Customer> queryo = (from c in db.CustomerEntities.Where(cusPred)
                                                               join p in db.PolicyEntities on c.CustomerID equals p.CustomerID
                                                               select new Customer
                                                               {
                                                                   CustomerId = c.CustomerID,
                                                                   FirstName = c.FirstName,
                                                                   LastName = c.LastName,
                                                                   Status = c.Status,
                                                                   IdNumber = c.IDNumber,
                                                                   IdType = c.IDType == null ? String.Empty : c.IDTypeEntity.Description,
                                                                   DOB = c.DOB == null ? string.Empty : c.DOB.Value.ToShortDateString(),
                                                                   ContactNo = c.ContractNo,
                                                                   MaritalStatus = c.MaritalStatus == null ? String.Empty : c.MaritalStatusEntity.Description,
                                                                   Profession = c.Profession,
                                                                   Country = c.Country == null ? String.Empty : c.CountryEntity.Description,
                                                                   State = c.State,
                                                                   OtherName = c.OtherName,
                                                                   HomeTelephone = c.HomeTelephone,
                                                                   OfficialTelephone = c.OfficeTelephone,
                                                                   EMail = c.Email,
                                                                   Mobile = c.Mobile,
                                                                   Address1 = c.Address1,
                                                                   Address2 = c.Address2,
                                                                   City = c.City,
                                                                   Gender = c.Gender,
                                                                   SoCode = p.SOCode,
                                                                   Remarks = c.Remarks,
                                                                   LastUpdated = c.LastUpdated == null ? string.Empty : c.LastUpdated.Value.ToShortDateString(),
                                                                   Title = c.Title == null ? String.Empty : c.SalutationEntity.Description.ToString()
                                                               }).Distinct();
                                return queryo.Skip((pageNo - 1) * pageSize).Take(pageSize).ToList();
                            }
                        }
                    }
                    else
                    {

                        if (str.Length > 1 && sb.Length > 1)
                        {

                            query = from c in db.CustomerEntities.Where(cusPred)
                                    join u in db.UserEntities.Where(sb.ToString())
                                    on c.SOCode equals u.SOCode
                                    join us in db.UsersInStructureEntities.Where(str.ToString())
                                    on u.UserId equals us.UserId
                                    select c;



                        }
                        else if (str.Length > 1 && sb.Length < 1)
                        {
                            query = from c in db.CustomerEntities.Where(cusPred)
                                    join u in db.UserEntities on c.SOCode equals u.SOCode
                                    join us in db.UsersInStructureEntities.Where(str.ToString())
                                    on u.UserId equals us.UserId
                                    select c;
                        }
                        else if (str.Length < 1 && sb.Length > 1)
                        {
                            query = from c in db.CustomerEntities.Where(cusPred)
                                    join u in db.UserEntities.Where(sb.ToString())
                                    on c.SOCode equals u.SOCode
                                    select c;

                        }
                        else if (str.Length < 1 && sb.Length < 1)
                        {
                            query = from c in db.CustomerEntities.Where(cusPred)
                                    select c;
                        }



                    }







                    //if (pol.Length > 1)
                    //{
                    //    if (str.Length > 1 && sb.Length > 1)
                    //    {
                    //        query = from c in db.CustomerEntities.Where(cus.ToString())
                    //                join u in db.UserEntities.Where(sb.ToString())
                    //                on c.SOCode equals u.SOCode
                    //                join us in db.UsersInStructureEntities.Where(str.ToString())
                    //                on u.UserId equals us.UserId
                    //                join po in db.PolicyEntities.Where(pol.ToString())
                    //                on c.CustomerID equals po.CustomerID
                    //                select c;
                    //    }
                    //    else if (str.Length > 1 && sb.Length < 1)
                    //    {
                    //        query = from c in db.CustomerEntities.Where(cus.ToString())
                    //                join u in db.UserEntities
                    //                on c.SOCode equals u.SOCode
                    //                join us in db.UsersInStructureEntities.Where(str.ToString())
                    //                on u.UserId equals us.UserId
                    //                join po in db.PolicyEntities.Where(pol.ToString())
                    //                on c.CustomerID equals po.CustomerID
                    //                select c;
                    //    }
                    //    else if (str.Length < 1 && sb.Length > 1)
                    //    {
                    //        query = from c in db.CustomerEntities.Where(cus.ToString())
                    //                join u in db.UserEntities.Where(sb.ToString())
                    //                on c.SOCode equals u.SOCode
                    //                join po in db.PolicyEntities.Where(pol.ToString())
                    //                on c.CustomerID equals po.CustomerID
                    //                select c;
                    //    }
                    //    else if (str.Length < 1 && sb.Length < 1)
                    //    {
                    //        query = from c in db.CustomerEntities.Where(cus.ToString())
                    //                join po in db.PolicyEntities.Where(pol.ToString())
                    //                on c.CustomerID equals po.CustomerID
                    //                select c;
                    //    }
                    //}
                    //else
                    //{
                    //    if (str.Length > 1 && sb.Length > 1)
                    //    {

                    //        query = from c in db.CustomerEntities.Where(cus.ToString())
                    //                join p in db.PolicyEntities on c.CustomerID equals p.CustomerID
                    //                join u in db.UserEntities.Where(sb.ToString())
                    //                on p.SOCode equals u.SOCode
                    //                join us in db.UsersInStructureEntities.Where(str.ToString())
                    //                on u.UserId equals us.UserId
                    //                select c;



                    //    }
                    //    else if (str.Length > 1 && sb.Length < 1)
                    //    {
                    //        query = from c in db.CustomerEntities.Where(cus.ToString())
                    //                join p in db.PolicyEntities on c.CustomerID equals p.CustomerID
                    //                join u in db.UserEntities on p.SOCode equals u.SOCode
                    //                join us in db.UsersInStructureEntities.Where(str.ToString())
                    //                on u.UserId equals us.UserId
                    //                select c;
                    //    }
                    //    else if (str.Length < 1 && sb.Length > 1)
                    //    {
                    //        query = from c in db.CustomerEntities.Where(cus.ToString())
                    //                join p in db.PolicyEntities on c.CustomerID equals p.CustomerID
                    //                join u in db.UserEntities.Where(sb.ToString())
                    //                on p.SOCode equals u.SOCode
                    //                select c;

                    //    }
                    //    else if (str.Length < 1 && sb.Length < 1)
                    //    {
                    //        query = from c in db.CustomerEntities.Where(cus.ToString())
                    //                select c;
                    //    }
                    //}




                    IQueryable<CustomerEntity> pageData = query.Skip((pageNo - 1) * pageSize).Take(pageSize);

                    return pageData.Select(c => Mapper.ToCustomerObject(c)).ToList();
                }
            }
            catch (Exception)
            {
                
                throw;
            }
            
            //}
            //else
            //    return null;
        }































        public Customer GetCustomerByProspectId(string prosId)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                CustomerEntity customer = (from cus in db.CustomerEntities
                                           join pros in db.ProspectEntities on cus.CustomerID equals pros.CustomerID
                                           where pros.ProspectID == prosId
                                           select cus).First();

                return Mapper.ToCustomerObject(customer);
            }
        }



    }
}
